﻿<%@ WebHandler Language="C#" Class="MainMaterialsConsumeHandler" %>

/*Description: 主材消耗定额处理程序
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer: Zhangrj 
'create Date:20240606
'Rewriter:
'Rewrite Date:
*/
using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Text;
using System.Linq;
using System.IO;
using Newtonsoft.Json;
using System.Collections.Generic;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
using System.Collections;
using NPOI.SS.UserModel;
using Camstar.WebClient.WebServicesProxy.InSiteWebServices;
using BusinessRule.Common;

public class MainMaterialsConsumeHandler : IHttpHandler, IRequiresSessionState
{
    RedisHelper redis = new RedisHelper();
    CurrentUserInfo m_UserInfo;
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    string rbFactoryID = System.Configuration.ConfigurationManager.AppSettings["RBFactoryID"].ToString(); //热表ID

    public void ProcessRequest(HttpContext context)
    {
        try
        {
            string action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            if (m_UserInfo == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "登录过期，请重新登录" }));
                return;
            }
            switch (action)
            {
                case "GetMainMaterialsConsumeData": //获取主材消耗定额维护数据
                    GetMainMaterialsConsumeData(context);
                    break;
                case "GetMaterialsConsumeInfoByID": //获取主材消耗定额维护单条数据（通过ID）
                    GetMaterialsConsumeInfoByID(context);
                    break;
                case "SaveMainMaterialsConsumeInfo": //保存主材消耗定额维护数据
                    SaveMainMaterialsConsumeInfo(context);
                    break;
                case "DeleMaterialsConsumeInfoByID": //删除主材消耗定额维护数据（通过ID）
                    DeleMaterialsConsumeInfoByID(context);
                    break;
                case "ImportMainMaterialsConsume": //导入主材消耗定额维护数据
                    ImportMainMaterialsConsume(context);
                    break;
                case "CalculateMaterialsYearDemand": //统计物料年度需求
                    CalculateMaterialsYearDemand(context);
                    break;
                case "ExportMainMaterialYearDemand": //导出物料年度需求
                    ExportMainMaterialYearDemand(context);
                    break;
                case "GetMainMaterialsMonthPlanData": //获取主材月度加工计划
                    GetMainMaterialsMonthPlanData(context);
                    break;
                case "ExportMainMaterialMonthPlan": //导出主材月度加工计划数据
                    ExportMainMaterialMonthPlan(context);
                    break;
                case "ExportMfgOrderMonthPlan": //导出计划订单数据
                    ExportMfgOrderMonthPlan(context);
                    break;
                default:
                    context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "未知功能" }));
                    break;
            }
        }
        catch (Exception ex)
        {
            uMESProcessResult result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    /// <summary>
    /// 导出计划订单数据
    /// </summary>
    /// <param name="context"></param>
    public void ExportMfgOrderMonthPlan(HttpContext context)
    {
        DataTable dtRes = JsonConvert.DeserializeObject<DataTable>(context.Request["params"].ToString());
        foreach (DataColumn item in dtRes.Columns)
        {
            switch (item.ColumnName)
            {
                case "MFGORDERNAME":
                    item.ColumnName = "生产订单"; break;
                case "PRODUCTNAME":
                    item.ColumnName = "零件号"; break;
                case "PRODUCTDESC":
                    item.ColumnName = "零件名称"; break;
                case "WORKFLOWSTEPNAME":
                    item.ColumnName = "工序"; break;
                case "QTY":
                    item.ColumnName = "数量"; break;
                case "PLANNEDSTARTDATE":
                    item.ColumnName = "计划开始日期"; break;
                case "PLANNEDCOMPLETIONDATE":
                    item.ColumnName = "计划结束日期"; break;
                default:
                    break;
            }
        }
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtRes);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("计划订单导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 导出主材月度加工计划数据
    /// </summary>
    /// <param name="context"></param>
    public void ExportMainMaterialMonthPlan(HttpContext context)
    {
        DataTable dtRes = JsonConvert.DeserializeObject<DataTable>(context.Request["params"].ToString());
        foreach (DataColumn item in dtRes.Columns)
        {
            switch (item.ColumnName)
            {
                case "MaterialName":
                    item.ColumnName = "物料编码"; break;
                case "MaterialDesc":
                    item.ColumnName = "物料名称"; break;
                case "CalculateUnit":
                    item.ColumnName = "计量单位"; break;
                case "MaterialTechStandard":
                    item.ColumnName = "技术标准"; break;
                case "MaterialVendormodel":
                    item.ColumnName = "牌号"; break;
                case "MaterialSpecification":
                    item.ColumnName = "规格"; break;
                case "MaterialCategory":
                    item.ColumnName = "类别"; break;
                case "DemandQty":
                    item.ColumnName = "需求数量"; break;
                case "InventoryQty":
                    item.ColumnName = "库存数量"; break;
                case "GapQty":
                    item.ColumnName = "缺口数量"; break;
                default:
                    break;
            }
        }
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtRes);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("主材月度加工计划导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 获取主材月度加工计划
    /// </summary>
    /// <param name="context"></param>
    public void GetMainMaterialsMonthPlanData(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        if (string.IsNullOrWhiteSpace(context.Request["planStartDate"].ToString()) || string.IsNullOrWhiteSpace(context.Request["planEndDate"].ToString()))
        {
            result.Result = false;
            result.Message = "请选择计划开始日期";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        DateTime nextStartDate = DateTime.Parse(context.Request["planStartDate"].ToString() + " 00:00:00");
        DateTime nextEndDate = DateTime.Parse(context.Request["planEndDate"].ToString() + " 23:59:59");
        StringBuilder sql = new StringBuilder();
        sql.AppendFormat(@"SELECT distinct m.mfgordername,m.plannedstartdate,m.plannedcompletiondate,m.qty,
                        pb.productname,p.description productdesc,wb.workflowname
                        FROM mfgorder m 
                        LEFT JOIN ORDERSTATUS os ON os.orderstatusid = m.orderstatusid
                        LEFT JOIN PRODUCTBASE pb1  ON pb1.PRODUCTBASEID = m.PRODUCTBASEID
                        LEFT JOIN PRODUCT P ON P.PRODUCTID = NVL(pb1.REVOFRCDID, m.PRODUCTID)
                        LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID
                        LEFT JOIN WORKFLOWBASE WB1 ON WB1.WORKFLOWBASEID = P.WORKFLOWBASEID
                        LEFT JOIN WORKFLOW W ON W.WORKFLOWID =NVL(WB1.REVOFRCDID,  P.WORKFLOWID)
                        LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID = W.WORKFLOWBASEID
                        LEFT JOIN workflowstep ws ON ws.workflowid = w.workflowid
                        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                        LEFT JOIN containercurrentinfo cc ON cc.mfgorderid = m.mfgorderid AND cc.containerstatus != 0
                        WHERE NVL(os.orderstatusname,'打开') = '打开' AND m.plannedstartdate >= to_date('{0}','yyyy-mm-dd hh24:mi:ss') 
                        AND m.plannedstartdate <= to_date('{1}','yyyy-mm-dd hh24:mi:ss') AND s.factoryid = '{2}'
                        AND DECODE(cc.containerid,NULL,0,cc.isinstore) != 1",
                        nextStartDate, nextEndDate, rbFactoryID);
        if (!string.IsNullOrWhiteSpace(context.Request["mfgOrderName"].ToString()))
        {
            sql.AppendFormat(" AND m.mfgordername like '%{0}%'", context.Request["mfgOrderName"].ToString());
        }
        if (!string.IsNullOrWhiteSpace(context.Request["productName"].ToString()))
        {
            sql.AppendFormat(" AND pb.productname like '%{0}%'", context.Request["productName"].ToString());
        }
        sql.AppendLine(" ORDER BY m.mfgordername");
        DataTable dtOrder = DatabaseHelper.Query_ReturnDatTable(sql.ToString());
        if (dtOrder.Rows.Count == 0)
        {
            result.Result = false;
            result.Message = "暂无数据";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        List<Dictionary<string, string>> dicList = new List<Dictionary<string, string>>();//最终计算结果
        double demandQty = 0;//需求数量
        double inventoryQty = 0;//库存数量
        double gapQty = 0;//缺口数量
        /*计算出所有物料的需求数*/
        foreach (DataRow drO in dtOrder.Rows)
        {
            sql.Clear();
            sql.AppendFormat("SELECT * FROM MainMaterialsConsumeInfo m WHERE m.productname = '{0}' AND m.status = 0", drO["productname"].ToString());
            DataTable dtMaterial = DatabaseHelper.Query_ReturnDatTable(sql.ToString());
            foreach (DataRow drM in dtMaterial.Rows)
            {
                Dictionary<string, string> dic = new Dictionary<string, string>();
                demandQty = double.Parse(drM["Singleconsumenum"].ToString()) * int.Parse(drO["qty"].ToString());//需求数量（单件消耗定额*订单数）
                if (dicList.Where(p => p["MaterialName"] == drM["materialname"].ToString()).Count() > 0)
                {
                    dic = dicList.Find(p => p["MaterialName"] == drM["materialname"].ToString());
                    dic["DemandQty"] = (double.Parse(dic["DemandQty"]) + demandQty).ToString();//需求数量累加
                }
                else
                {
                    dic.Add("MaterialName", drM["MaterialName"].ToString());
                    dic.Add("MaterialDesc", drM["MaterialDesc"].ToString());
                    dic.Add("MaterialVendormodel", drM["materialvendormodel"].ToString());
                    dic.Add("MaterialSpecification", drM["materialspecification"].ToString());
                    dic.Add("MaterialTechStandard", drM["materialtechstandard"].ToString());
                    dic.Add("MaterialCategory", drM["materialcategory"].ToString());
                    dic.Add("CalculateUnit", drM["calculateunit"].ToString());
                    dic.Add("DemandQty", demandQty.ToString());
                    dicList.Add(dic);
                }
            }
        }
        /*计算出物料的其他数量*/
        foreach (Dictionary<string, string> item in dicList)
        {
            sql.Clear();
            /*获取物料的库存数量*/
            sql.AppendFormat(@"SELECT sum(ax.qty) qty FROM auxmaterial_stock_info ax
                        WHERE ax.auxmaterialname = '{0}'
                        GROUP BY ax.auxmaterialname", item["MaterialName"].ToString());
            DataTable dtInventoryQty = DatabaseHelper.Query_ReturnDatTable(sql.ToString());
            inventoryQty = dtInventoryQty.Rows.Count == 0 ? 0 : int.Parse(dtInventoryQty.Rows[0]["qty"].ToString());//库存数量（取线边库中库存数）
            gapQty = double.Parse(item["DemandQty"].ToString()) - inventoryQty;//缺口数量（需求数量-库存数量）
            gapQty = gapQty < 0 ? 0 : gapQty;//如果小于0，则显示为0
            item.Add("InventoryQty", inventoryQty.ToString());
            item.Add("GapQty", gapQty.ToString());
        }
        result.DBTable = dtOrder;
        result.ReturnData = dicList;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 导出主材年度需求统计数据
    /// </summary>
    /// <param name="context"></param>
    public void ExportMainMaterialYearDemand(HttpContext context)
    {
        DataTable dtRes = JsonConvert.DeserializeObject<DataTable>(context.Request["params"].ToString());
        foreach (DataColumn item in dtRes.Columns)
        {
            switch (item.ColumnName)
            {
                case "MaterialName":
                    item.ColumnName = "物料编码"; break;
                case "MaterialDesc":
                    item.ColumnName = "物料名称"; break;
                case "CalculateUnit":
                    item.ColumnName = "计量单位"; break;
                case "MaterialTechStandard":
                    item.ColumnName = "技术标准"; break;
                case "MaterialVendormodel":
                    item.ColumnName = "牌号"; break;
                case "MaterialSpecification":
                    item.ColumnName = "规格"; break;
                case "MaterialCategory":
                    item.ColumnName = "类别"; break;
                case "DemandQty":
                    item.ColumnName = "需求数量"; break;
                case "InventoryQty":
                    item.ColumnName = "库存数量"; break;
                case "ApplyQty":
                    item.ColumnName = "申请数量"; break;
                case "StandardPrice":
                    item.ColumnName = "标准价格"; break;
                case "Money":
                    item.ColumnName = "金额"; break;
                default:
                    break;
            }
        }
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtRes);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("主材年度需求统计导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 统计物料年度需求
    /// </summary>
    /// <param name="context"></param>
    public void CalculateMaterialsYearDemand(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        DataTable dtModel = JsonConvert.DeserializeObject<DataTable>(context.Request["modelList"].ToString());
        if (dtModel.Rows.Count == 0)
        {
            result.Result = false; result.Message = "请添加需计算的型号数据";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        if (dtModel.AsEnumerable().GroupBy(p => p["modelname"].ToString()).Select(p => p.First()).Count() != dtModel.Rows.Count)
        {
            result.Result = false; result.Message = "添加了重复型号";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        List<Dictionary<string, string>> dicList = new List<Dictionary<string, string>>();//最终计算结果
        string sql = string.Empty;
        double demandQty = 0;//需求数量（型号数量*单件消耗定额*单台数量）
        double inventoryQty = 0;//库存数量（取线边库中对应数据）
        double applyQty = 0;//申请数量（需求数量-库存数量-上年度计划未到货数量）
        double standardPrice = 0;//标准价格（取线边库中对应数据）
        double money = 0;//金额（标准价格*申请数量）
        /*计算出所有的物料的需求数*/
        foreach (DataRow item in dtModel.Rows)
        {
            sql = string.Format(@"SELECT m.materialname,m.materialdesc,m.singleconsumenum,m.singledemandnum,m.materialvendormodel,m.calculateunit,
            m.materialspecification,m.materialtechstandard,m.materialcategory FROM  MainMaterialsConsumeInfo m WHERE m.modelname = '{0}' AND m.status = 0", item["modelname"].ToString());
            DataTable dtMarterial = DatabaseHelper.Query_ReturnDatTable(sql);
            if (dtMarterial.Rows.Count == 0)
            {
                result.Result = false; result.Message = string.Format("系统中未找到型号：{0}", item["modelname"].ToString());
                break;
            }
            int qty = int.Parse(item["qty"].ToString());//型号数量
            foreach (DataRow drMarterial in dtMarterial.Rows)
            {
                Dictionary<string, string> dic = new Dictionary<string, string>();
                demandQty = Math.Round((int.Parse(drMarterial["singledemandnum"].ToString()) * qty * double.Parse(drMarterial["singleconsumenum"].ToString())), 2);//需求数量
                if (dicList.Where(p => p["MaterialName"] == drMarterial["materialname"].ToString()).Count() > 0)
                {
                    dic = dicList.Find(p => p["MaterialName"] == drMarterial["materialname"].ToString());
                    dic["DemandQty"] = (double.Parse(dic["DemandQty"]) + demandQty).ToString();//需求数量
                }
                else
                {
                    dic.Add("MaterialName", drMarterial["MaterialName"].ToString());
                    dic.Add("MaterialDesc", drMarterial["MaterialDesc"].ToString());
                    dic.Add("MaterialVendormodel", drMarterial["materialvendormodel"].ToString());
                    dic.Add("MaterialSpecification", drMarterial["materialspecification"].ToString());
                    dic.Add("MaterialTechStandard", drMarterial["materialtechstandard"].ToString());
                    dic.Add("MaterialCategory", drMarterial["materialcategory"].ToString());
                    dic.Add("CalculateUnit", drMarterial["calculateunit"].ToString());
                    dic.Add("DemandQty", demandQty.ToString());//需求数量
                    dicList.Add(dic);
                }
            }
        }
        /*计算出物料的其他数量*/
        foreach (Dictionary<string, string> item in dicList)
        {
            /*获取物料的库存数量*/
            sql = string.Format(@"SELECT sum(ax.qty) qty,max(ax.standardprice) standardprice FROM auxmaterial_stock_info ax
                        WHERE ax.auxmaterialname = '{0}'
                        GROUP BY ax.auxmaterialname", item["MaterialName"].ToString());
            DataTable dtMarterialStock = DatabaseHelper.Query_ReturnDatTable(sql.ToString());
            inventoryQty = 0;
            standardPrice = 0;
            if (dtMarterialStock.Rows.Count > 0)
            {
                inventoryQty = int.Parse(dtMarterialStock.Rows[0]["qty"].ToString());//库存数量（取线边库中库存数）
                standardPrice = int.Parse(dtMarterialStock.Rows[0]["standardprice"].ToString());//标准价格
            }
            applyQty = double.Parse(item["DemandQty"]) - inventoryQty - 0;//申请数量（需求数量-库存数量-上年度计划未到货数量）
            //applyQty = applyQty < 0 ? 0 : applyQty;//如果小于0，则显示为0
            money = standardPrice * applyQty;//金额 （标准价格*申请数量）
            item.Add("InventoryQty", inventoryQty.ToString());
            item.Add("ApplyQty", applyQty.ToString());
            item.Add("StandardPrice", standardPrice.ToString());
            item.Add("Money", money.ToString());
        }
        if (result.Result)
        {
            result.ReturnData = dicList;
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 导入主材消耗定额数据
    /// </summary>
    /// <param name="context"></param>
    public void ImportMainMaterialsConsume(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = Path.GetExtension(file.FileName);
            string fileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除

            IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析文件出错", Result = false }));
                return;
            }
            DeleteFile(fpath);
            DataTable dtImport = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "导入成功" };
            if (dtImport.Rows.Count == 0)
            {
                result.Result = false; result.Message = "解析内容出错";
                context.Response.Write(JsonConvert.SerializeObject(result));
                return;
            }
            string[] strName = { "型号", "零件号", "物料编码", "物料名称", "牌号", "规格", "技术标准", "类别", "单件消耗定额", "计量单位", "单台数量", "备注" };
            foreach (string str in strName)
            {
                if (!dtImport.Columns.Contains(str))
                {
                    result.Result = false; result.Message = string.Format("导入模板表头有误，缺少{0}", str);
                    break;
                }
            }
            if (!result.Result)
            {
                context.Response.Write(JsonConvert.SerializeObject(result));
                return;
            }
            result.DBTable = SaveImportMainMaterialsConsume(dtImport);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }

    /// <summary>
    /// 保存导入数据
    /// </summary>
    /// <param name="dtImport"></param>
    /// <returns></returns>
    private DataTable SaveImportMainMaterialsConsume(DataTable dtImport)
    {
        StringBuilder sqlBd = new StringBuilder();
        ArrayList sqlList = new ArrayList();
        dtImport.Columns.Add("IsResult");
        dtImport.Columns.Add("ResultMessage");
        foreach (DataRow item in dtImport.Rows)
        {
            if (string.IsNullOrWhiteSpace(item["零件号"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "零件号为空";
                continue;
            }
            if (string.IsNullOrWhiteSpace(item["物料编码"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "物料编码为空";
                continue;
            }
            if (string.IsNullOrWhiteSpace(item["型号"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "型号为空";
                continue;
            }
            if (string.IsNullOrWhiteSpace(item["单件消耗定额"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "单件消耗定额为空";
                continue;
            }
            if (string.IsNullOrWhiteSpace(item["单台数量"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "单台数量为空";
                continue;
            }
            string sql = string.Format("SELECT 1 FROM productbase pb WHERE pb.productname = '{0}'", item["零件号"].ToString());
            if (DatabaseHelper.GetSingle(sql) == null)
            {
                item["IsResult"] = false; item["ResultMessage"] = "未在系统中找到件号";
                continue;
            }
            //sql = string.Format("SELECT 1 FROM auxmaterial a WHERE a.auxmaterialname = '{0}'", item["物料编码"].ToString());
            //if (DatabaseHelper.GetSingle(sql) == null)
            //{
            //    item["IsResult"] = false; item["ResultMessage"] = "未在系统中找到物料编码";
            //    continue;
            //}
            sql = string.Format("SELECT 1 FROM MainMaterialsConsumeInfo m WHERE m.Productname = '{0}' AND m.Materialname = '{1}' AND m.Status = 0", item["零件号"].ToString(), item["物料编码"].ToString());
            if (DatabaseHelper.GetSingle(sql) != null)
            {
                item["IsResult"] = false; item["ResultMessage"] = "存在相同的件号和物料编码";
                continue;
            }
            if (double.Parse(item["单件消耗定额"].ToString()) <= 0)
            {
                item["IsResult"] = false; item["ResultMessage"] = "单件消耗定额必须大于0";
                continue;
            }
            if (int.Parse(item["单台数量"].ToString()) <= 0)
            {
                item["IsResult"] = false; item["ResultMessage"] = "单台数量必须大于0";
                continue;
            }
            sqlBd.Clear();
            sqlBd.AppendLine(@"INSERT INTO MainMaterialsConsumeInfo(Mainmaterialsconsumeinfoid,Modelname,Productname,Materialname,
                               Materialdesc,Materialvendormodel,Materialspecification,Materialtechstandard,Materialcategory,
                               Singleconsumenum,Calculateunit,Singledemandnum,Createtime,Createby,Status,Remark) VALUES(");
            sqlBd.AppendFormat("'{0}',", Guid.NewGuid().ToString());
            sqlBd.AppendFormat("'{0}',", item["型号"].ToString());
            sqlBd.AppendFormat("'{0}',", item["零件号"].ToString());
            sqlBd.AppendFormat("'{0}',", item["物料编码"].ToString());
            sqlBd.AppendFormat("'{0}',", item["物料名称"].ToString());
            sqlBd.AppendFormat("'{0}',", item["牌号"].ToString());
            sqlBd.AppendFormat("'{0}',", item["规格"].ToString());
            sqlBd.AppendFormat("'{0}',", item["技术标准"].ToString());
            sqlBd.AppendFormat("'{0}',", item["类别"].ToString());
            sqlBd.AppendFormat("{0},", double.Parse(item["单件消耗定额"].ToString()));
            sqlBd.AppendFormat("'{0}',", item["计量单位"].ToString());
            sqlBd.AppendFormat("{0},", int.Parse(item["单台数量"].ToString()));
            sqlBd.AppendLine("sysdate,");
            sqlBd.AppendFormat("'{0}',", m_UserInfo.EmployeeID);
            sqlBd.AppendLine("0,");
            sqlBd.AppendFormat("'{0}')", item["备注"].ToString());
            sqlList.Add(sqlBd.ToString());
            item["IsResult"] = true; item["ResultMessage"] = "导入成功";
        }
        DatabaseHelper.ExecuteSqlTran(sqlList);
        return dtImport;
    }

    /// <summary>
    /// 获取主材消耗定额维护数据
    /// </summary>
    /// <param name="context"></param>
    public void GetMainMaterialsConsumeData(HttpContext context)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine(" WHERE mc.status = 0");
        //查询型号
        if (!string.IsNullOrWhiteSpace(context.Request["modelName"].ToString()))
        {
            sql.AppendFormat(" AND mc.modelname like '%{0}%'", context.Request["modelName"].ToString());
        }
        //查询件号
        if (!string.IsNullOrWhiteSpace(context.Request["productName"].ToString()))
        {
            sql.AppendFormat(" AND pb.productname like '%{0}%'", context.Request["productName"].ToString());
        }
        //查询物料编码
        if (!string.IsNullOrWhiteSpace(context.Request["materialName"].ToString()))
        {
            sql.AppendFormat(" AND mc.materialName LIKE '%{0}%'", context.Request["materialName"].ToString());
        }
        uMESPagingDataDTO pageDto = new uMESPagingDataDTO();
        pageDto.CurrentPageIndex = int.Parse(context.Request["pageIndex"].ToString());
        pageDto.PageSize = int.Parse(context.Request["pageSize"].ToString());
        pageDto.strSQL = GetMainMaterialsConsumeInfoSql(sql.ToString());
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(pageDto);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 获取主材消耗定额维护数据
    /// </summary>
    /// <param name="context"></param>
    public void GetMaterialsConsumeInfoByID(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        result.DBTable = DatabaseHelper.Query_ReturnDatTable(GetMainMaterialsConsumeInfoSql(string.Format(" WHERE mc.mainmaterialsconsumeinfoid = '{0}'", context.Request["materialID"].ToString())));
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 获取主材消耗定额SQL
    /// </summary>
    /// <param name="sqlWhere"></param>
    /// <returns></returns>
    public string GetMainMaterialsConsumeInfoSql(string sqlWhere)
    {
        return @"SELECT Distinct mc.*,p.description,e.fullname createbyname,em.fullname updatebyname
                FROM mainMaterialsConsumeInfo mc 
                INNER JOIN productbase pb ON pb.productname = mc.productname
                LEFT JOIN product p ON p.productid = pb.revofrcdid
                LEFT JOIN auxmaterial am ON am.auxmaterialname = mc.materialname
                LEFT JOIN employee e ON e.employeeid = mc.createby
                LEFT JOIN employee em ON em.employeeid = mc.updateby " + sqlWhere;
    }

    /// <summary>
    /// 保存主材消耗定额
    /// </summary>
    /// <param name="context"></param>
    public void SaveMainMaterialsConsumeInfo(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult();
        Dictionary<string, string> param = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        string sql = string.Format("SELECT 1 FROM productbase pb WHERE pb.productname = '{0}'", param["productName"]);
        if (DatabaseHelper.GetSingle(sql) == null)
        {
            result.Result = false; result.Message = "未在系统中找到件号";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        //sql = string.Format("SELECT 1 FROM auxmaterial a WHERE a.auxmaterialname = '{0}'", param["materialName"]);
        //if (DatabaseHelper.GetSingle(sql) == null)
        //{
        //    result.Result = false; result.Message = "未在系统中找到物料编码";
        //    context.Response.Write(JsonConvert.SerializeObject(result));
        //    return;
        //}
        if (double.Parse(param["singleConsumeNum"]) <= 0)
        {
            result.Result = false; result.Message = "单件消耗定额必须大于0";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        if (int.Parse(param["singleDemandNum"]) <= 0)
        {
            result.Result = false; result.Message = "单台数量必须大于0";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        StringBuilder sqlBd = new StringBuilder();
        if (string.IsNullOrWhiteSpace(param["materialID"])) //如果ID为空，则添加
        {
            sql = string.Format("SELECT 1 FROM MainMaterialsConsumeInfo m WHERE m.Productname = '{0}' AND m.Materialname = '{1}' AND m.Status = 0", param["productName"], param["materialName"]);
            if (DatabaseHelper.GetSingle(sql) != null)
            {
                result.Result = false; result.Message = "存在相同的件号和物料编码";
                context.Response.Write(JsonConvert.SerializeObject(result));
                return;
            }
            sqlBd.AppendLine(@"INSERT INTO MainMaterialsConsumeInfo(Mainmaterialsconsumeinfoid,Modelname,Productname,Materialname,
                               Materialdesc,Materialvendormodel,Materialspecification,Materialtechstandard,Materialcategory,
                               Singleconsumenum,Calculateunit,Singledemandnum,Createtime,Createby,Status,Remark) VALUES(");
            sqlBd.AppendFormat("'{0}',", Guid.NewGuid().ToString());
            sqlBd.AppendFormat("'{0}',", param["modelName"]);
            sqlBd.AppendFormat("'{0}',", param["productName"]);
            sqlBd.AppendFormat("'{0}',", param["materialName"]);
            sqlBd.AppendFormat("'{0}',", param["materialDesc"]);
            sqlBd.AppendFormat("'{0}',", param["materialVendorModel"]);
            sqlBd.AppendFormat("'{0}',", param["materialSpecification"]);
            sqlBd.AppendFormat("'{0}',", param["materialTechStandard"]);
            sqlBd.AppendFormat("'{0}',", param["materialCategory"]);
            sqlBd.AppendFormat("{0},", double.Parse(param["singleConsumeNum"]));
            sqlBd.AppendFormat("'{0}',", param["calculateUnit"]);
            sqlBd.AppendFormat("{0},", int.Parse(param["singleDemandNum"]));
            sqlBd.AppendLine("sysdate,");
            sqlBd.AppendFormat("'{0}',", m_UserInfo.EmployeeID);
            sqlBd.AppendLine("0,");
            sqlBd.AppendFormat("'{0}')", param["remark"]);
        }
        else
        {
            sqlBd.AppendLine("UPDATE MainMaterialsConsumeInfo m SET ");
            sqlBd.AppendFormat("m.modelname = '{0}',", param["modelName"]);
            sqlBd.AppendFormat("m.productname = '{0}',", param["productName"]);
            sqlBd.AppendFormat("m.materialName = '{0}',", param["materialName"]);
            sqlBd.AppendFormat("m.Materialdesc = '{0}',", param["materialDesc"]);
            sqlBd.AppendFormat("m.Materialvendormodel = '{0}',", param["materialVendorModel"]);
            sqlBd.AppendFormat("m.Materialspecification = '{0}',", param["materialSpecification"]);
            sqlBd.AppendFormat("m.Materialtechstandard = '{0}',", param["materialTechStandard"]);
            sqlBd.AppendFormat("m.Materialcategory = '{0}',", param["materialCategory"]);
            sqlBd.AppendFormat("m.Singleconsumenum = {0},", double.Parse(param["singleConsumeNum"]));
            sqlBd.AppendFormat("m.calculateUnit = '{0}',", param["calculateUnit"]);
            sqlBd.AppendFormat("m.Singledemandnum = {0},", int.Parse(param["singleDemandNum"]));
            sqlBd.AppendFormat("m.Remark = '{0}',", param["remark"]);
            sqlBd.AppendLine("m.updatetime = sysdate,");
            sqlBd.AppendFormat("m.updateby = '{0}' ", m_UserInfo.EmployeeID);
            sqlBd.AppendFormat("WHERE m.mainmaterialsconsumeinfoid = '{0}'", param["materialID"]);
        }
        DatabaseHelper.ExecuteSql(sqlBd.ToString());
        result.Result = true;
        result.Message = "保存成功";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 删除主材消耗定额数据（通过ID）
    /// </summary>
    /// <param name="context"></param>
    public void DeleMaterialsConsumeInfoByID(HttpContext context)
    {
        DataTable dtMaterial = JsonConvert.DeserializeObject<DataTable>(context.Request["datas"].ToString());
        string materialIDs = string.Empty;
        dtMaterial.AsEnumerable().ToList().ForEach(p =>
        {
            materialIDs += string.Format("'{0}',", p["materialID"].ToString());
        });
        materialIDs = materialIDs.TrimEnd(',');
        string sql = string.Format("UPDATE MainMaterialsConsumeInfo M SET M.Status = 1, M.updatetime = SYSDATE, M.updateby = '{0}' WHERE M.mainmaterialsconsumeinfoid IN ({1})", m_UserInfo.EmployeeID, materialIDs);
        DatabaseHelper.ExecuteSql(sql);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "删除成功" }));
    }

    /// <summary>
    /// 删除文件
    /// </summary>
    /// <param name="fpath">文件地址</param>
    private void DeleteFile(string fpath)
    {
        if (File.Exists(fpath))
        {
            new FileInfo(fpath).Attributes = FileAttributes.Normal;
            File.Delete(fpath);
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}